package org.itworks.biodev.model;

import java.sql.*;
import java.util.*;


public class ProductTable extends AbstractTable<Product> {

	
	private static ProductTable productTable;
	
	public static ProductTable getInstance() {
		if(productTable == null) {
			productTable = new ProductTable();
		}
		
		return productTable;
	}
	
	@Override
	public boolean add(Product product) {
		boolean retCode = false;
		int nextId = getNextId();
		if(product != null) {
			try {
				String sqlQuery = "INSERT INTO product VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
				PreparedStatement preparedStatement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
				preparedStatement.setInt(1, nextId);
				preparedStatement.setString(2, product.getLabel());
				preparedStatement.setInt(3, product.getRealQuantity());
				preparedStatement.setInt(4, product.getLogicalQuantity());
				preparedStatement.setDouble(5, product.getPrice());
				preparedStatement.setInt(6, product.getThresholdWarningQuantity());
				preparedStatement.setInt(7, product.getUnitQuantity());
				preparedStatement.setInt(8, product.getSubCategoryId());
				preparedStatement.setDouble(9, product.getVatRate().getValue());
				
				retCode = preparedStatement.executeUpdate() > 0;
				
				preparedStatement.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		if(retCode) {
			product.setId(nextId);
		}
		return retCode;
	}

	@Override
	public boolean delete(Product object) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean update(Product object) {
		// TODO Auto-generated method stub
		return false;
	}
	
	public Product find(int id) throws Exception {
		Product product = null;
		Statement statement = BiodevDatabase.getConnection().createStatement();
		ResultSet result = statement.executeQuery("SELECT * FROM product WHERE id = " + id);
		if(result.next()) {
			product = new Product();
			product.setId( Integer.parseInt(result.getObject("id").toString()) );
			product.setLabel(result.getObject("label").toString() );
			product.setLogicalQuantity( Integer.parseInt( result.getObject("logical_quantity").toString()) );
			product.setRealQuantity(Integer.parseInt( result.getObject("real_quantity").toString()));
			product.setThresholdWarningQuantity(Integer.parseInt(result.getObject("threshold_warning_quantity").toString()));
			product.setPrice(Float.parseFloat(result.getObject("price").toString()));
			product.setUnit(findUnitFromProduct(product));
			product.setUnitQuantity(Integer.parseInt( result.getObject("unit_quantity").toString()) );
			product.setSubCategoryId(Integer.parseInt(result.getObject("sub_category_id").toString()));
		}
		
		return product;
	}
	
	public VATRate findVatRatFromProduct(Product p) throws Exception {
		VATRate vatRate = null;
		String sql = "SELECT v.value FROM product p, vat_rate v WHERE p.vat_rate = v.value AND p.id = ?";
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sql);
		statement.setInt(1, p.getId());
		ResultSet resultSet = statement.executeQuery();
		if(resultSet.next()) {
			vatRate = new VATRate();
			vatRate.setValue(Double.parseDouble(resultSet.getObject(1).toString()));
		}
		return vatRate;
	}
	
	public Unit findUnitFromProduct(Product p) throws Exception {
		// R�cup�rer l'�ventuelle liaison unit� produit */
		Unit unit = null;
		Statement statement = BiodevDatabase.getConnection().createStatement();
		ResultSet result = statement.executeQuery("SELECT unit_id FROM product_unit WHERE product_id = " + p.getId());
		
		if(result.next()) {
			unit = UnitTable.getInstance().find(Integer.parseInt(result.getObject("unit_id").toString()));
			
		}
		
		return unit;
	}
	
	public ProductSubCategory findSubCategoryFromProduct(Product p) throws Exception {
		ProductSubCategory productSubCategory = null;
		Statement statement = BiodevDatabase.getConnection().createStatement();
		ResultSet resultSet = statement.executeQuery("SELECT * FROM product_sub_category WHERE id = " + p.getSubCategoryId());
		
		if(resultSet.next()) {
			productSubCategory = new ProductSubCategory();
			productSubCategory.setId(Integer.parseInt(resultSet.getObject("id").toString()));
			productSubCategory.setLabel(resultSet.getObject("label").toString());
			productSubCategory.setCategoryId(Integer.parseInt(resultSet.getObject("product_category_id").toString()));
		}
		
		return productSubCategory;
	}
	
	
	public Vector<Product> findAll() throws Exception {
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		Statement statement = BiodevDatabase.getConnection().createStatement();
		ResultSet resultSet = statement.executeQuery("SELECT * FROM product");
		
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			currentProduct.setSubCategoryId(Integer.parseInt(resultSet.getObject("sub_category_id").toString()));
			
			products.addElement(currentProduct);
		}
		
		return products;
	}

	public Vector<Product> findByLabel(String productLabel) throws SQLException, Exception {
		Product currentProduct = null;
		Vector<Product> products = new Vector<Product>();
		
		String sql = "SELECT * FROM product WHERE UPPER(label) LIKE ?";
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sql);
		statement.setString(1, "%"+productLabel+"%");
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			currentProduct.setSubCategoryId(Integer.parseInt(resultSet.getObject("sub_category_id").toString()));
			
			products.addElement(currentProduct);
		}
		
		return products;
	}

	public Vector<Product> findAllDifferentFromOrder(Vector<ProductOrder> orderedProducts) throws Exception {
		String sqlQuery;
		if(orderedProducts.size() > 0) {
			String forbidden = orderedProducts.elementAt(0).getProductId()+"";
			for(int i = 1; i < orderedProducts.size(); i++) {
				forbidden += ","+ orderedProducts.elementAt(i).getProductId();
			}
			sqlQuery = "SELECT * FROM product WHERE id NOT IN (" + forbidden + ")";
		} else {
			sqlQuery = "SELECT * FROM product";
		}
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		ResultSet resultSet = statement.executeQuery();
		
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			currentProduct.setSubCategoryId(Integer.parseInt(resultSet.getObject("sub_category_id").toString()));
			products.addElement(currentProduct);
		}
		
		return products;
	}
	
	public Vector<Product> findAllDifferentFromSale(Vector<ProductSale> orderedProducts) throws Exception {
		String sqlQuery;
		if(orderedProducts.size() > 0) {
			String forbidden = orderedProducts.elementAt(0).getProductId()+"";
			for(int i = 1; i < orderedProducts.size(); i++) {
				forbidden += ","+ orderedProducts.elementAt(i).getProductId();
			}
			sqlQuery = "SELECT * FROM product WHERE id NOT IN (" + forbidden + ")";
		} else {
			sqlQuery = "SELECT * FROM product";
		}
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		ResultSet resultSet = statement.executeQuery();
		
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			currentProduct.setSubCategoryId(Integer.parseInt(resultSet.getObject("sub_category_id").toString()));
			products.addElement(currentProduct);
		}
		
		return products;
	}
	
	
	public Vector<Product> findAllDifferentFromOrderByLabel(Order order, String label) throws Exception {
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		String sqlQuery = "SELECT * FROM product WHERE id NOT IN (" + "SELECT product_id FROM PRODUCT_ORDER WHERE supplier_order_id = ?" + ") AND UPPER(label) LIKE ?";
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		statement.setInt(1, order.getId());
		statement.setString(2, "%"+label+"%");
		ResultSet resultSet = statement.executeQuery();
		
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			
			products.addElement(currentProduct);
		}
		
		return products;
	}
	
	public Vector<Product> findAllDifferentFromSaleByLabel(Sale sale, String label) throws Exception {
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		String sqlQuery = "SELECT * FROM product WHERE id NOT IN (" + "SELECT product_id FROM SALE_PRODUCT WHERE sale_id = ?" + ") AND UPPER(label) LIKE ?";
		PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		statement.setInt(1, sale.getId());
		statement.setString(2, "%"+label+"%");
		ResultSet resultSet = statement.executeQuery();
		
		while(resultSet.next()) {
			currentProduct = new Product();
			currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
			currentProduct.setLabel(resultSet.getObject("label").toString() );
			currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
			currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
			currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
			currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
			
			currentProduct.setUnit(findUnitFromProduct(currentProduct));
			currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
			
			products.addElement(currentProduct);
		}
		
		return products;
	}
	
	public Vector<Product> findAllByNameLikeAndNotInProductOrders(String productLabel, Vector<ProductOrder> productOrders) throws Exception {
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		String sqlQuery;
		if(productOrders != null) {
			if(productOrders.size() > 0) {
				
				String forbidden = productOrders.elementAt(0).getProductId()+"";
				for(int i = 1; i < productOrders.size(); i++) {
					forbidden += ","+ productOrders.elementAt(i).getProductId();
				}
				
				sqlQuery = "SELECT * FROM product WHERE UPPER(label) LIKE ? AND id NOT IN (" + forbidden + ")";
			} else {
				sqlQuery = "SELECT * FROM product WHERE UPPER(label) LIKE ?";
			}
			PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
			statement.setString(1, "%"+productLabel.toUpperCase()+"%");
			ResultSet resultSet = statement.executeQuery();
			
			while(resultSet.next()) {
				currentProduct = new Product();
				currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
				currentProduct.setLabel(resultSet.getObject("label").toString() );
				currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
				currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
				currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
				currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
				
				currentProduct.setUnit(findUnitFromProduct(currentProduct));
				currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
				
				products.addElement(currentProduct);
			}
		}
		
		return products;
	}
	
	public Vector<Product> findAllByNameLikeAndNotInProductSales(String productLabel, Vector<ProductSale> productSales) throws Exception {
		Vector<Product> products = new Vector<Product>();
		Product currentProduct = null;
		String sqlQuery;
		if(productSales != null) {
			if(productSales.size() > 0) {
				
				String forbidden = productSales.elementAt(0).getProductId()+"";
				for(int i = 1; i < productSales.size(); i++) {
					forbidden += ","+ productSales.elementAt(i).getProductId();
				}
				
				sqlQuery = "SELECT * FROM product WHERE UPPER(label) LIKE ? AND id NOT IN (" + forbidden + ")";
			} else {
				sqlQuery = "SELECT * FROM product WHERE UPPER(label) LIKE ?";
			}
			PreparedStatement statement = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
			statement.setString(1, "%"+productLabel.toUpperCase()+"%");
			ResultSet resultSet = statement.executeQuery();
			
			while(resultSet.next()) {
				currentProduct = new Product();
				currentProduct.setId( Integer.parseInt(resultSet.getObject("id").toString()) );
				currentProduct.setLabel(resultSet.getObject("label").toString() );
				currentProduct.setLogicalQuantity( Integer.parseInt( resultSet.getObject("logical_quantity").toString()) );
				currentProduct.setRealQuantity(Integer.parseInt( resultSet.getObject("real_quantity").toString()));
				currentProduct.setThresholdWarningQuantity(Integer.parseInt(resultSet.getObject("threshold_warning_quantity").toString()));
				currentProduct.setPrice(Float.parseFloat(resultSet.getObject("price").toString()));
				
				currentProduct.setUnit(findUnitFromProduct(currentProduct));
				currentProduct.setUnitQuantity(Integer.parseInt( resultSet.getObject("unit_quantity").toString()) );
				
				products.addElement(currentProduct);
			}
		}
		
		return products;
	}
	
	public static int getNextId() {
		int id = 1;
		try {
			String sql = "SELECT MAX(id) as mid FROM product";
			Statement st = BiodevDatabase.getConnection().createStatement();
			
			ResultSet resultSet = st.executeQuery(sql);
			if(resultSet.next()) {
				if(resultSet.getObject("mid") != null) {
					id = Integer.parseInt(resultSet.getObject("mid").toString()) + 1;
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return id;
	}
}
